import mysql from 'mysql2';

class Database {
  constructor() {
    // 创建 MySQL 连接池，代替 connection 对象
    this.conn = mysql.createPool({
      host: 'localhost',
      user: 'root',
      password: '',
      database: 'demo',
      waitForConnections: true,
      connectionLimit: 10, // 连接池最大连接数
      maxIdle: 10, //
      
    });
  }
  /**
   * 执行 SQL 查询语句
   * @param {string | string[]} sql 需要执行的 sql 语句
   * @param  {...any} params 参数
   * @returns { Promise<any[]> }
   */
  #query(sql, ...params) {
    // 兼容模板标签函数
    if (Array.isArray(sql))
      sql = String.raw({raw: sql}, ...Array(params.length).fill('?'));
    else {
      if (params.length === 1 && Array.isArray(params[0])) {
        params = params[0];
      }
    }
    return new Promise((resolve, reject) => {
      this.conn.execute(sql, params, (err, results) => {
        if (err) {
          reject(err);
        } else {
          resolve(results);
        }
      });
    });
  }

  async findMany(sql, ...params) {
    return this.#query(sql, ...params);
  }
  
  async findOne(sql, ...params) {
    const results = await this.#query(sql, ...params);
    return results[0];
  }

  async insert(sql, ...params) {
    const { insertId } = await this.#query(sql, ...params);
    if (Array.isArray(sql)) sql = String.raw({raw: sql}, ...Array(params.length).fill('?'));
    return this.findOne(`SELECT * FROM ${getTableName(sql)} WHERE id = ${insertId}`);
  }

  async update(sql, ...params) {
    const { affectedRows } = await this.#query(sql, ...params);
    return affectedRows
  }
  
  async delete(sql, ...params) {
    const { affectedRows } = await this.#query(sql, ...params);
    return affectedRows
  }

  end() {
    this.conn.end();
  }
}

function getTableName(sql) {
    const regex = /INSERT\s+INTO\s+([^\s\(]+)/i;
    const match = sql.match(regex);
    return match ? match[1] : null;
}

const db = new Database();
const username = 'zhangsan';
const email = '<EMAIL>';
const password = '哇哈哈';

// 查询多条记录
const reuslts = await db.findMany`select * from users`;
console.log(reuslts);
// 查询单条记录
const result = await db.findOne`select * from users where id = ${1}`;
console.log(result);
// 插入一条记录
const rs1 = await db.insert`insert into users (username, email, password) values (${username}, ${email}, ${password})`;
console.log(rs1);
// 删除一条记录
const rs2 = await db.delete`delete from users where id = ${1}`;
console.log(rs2);
// 更新用户记录
const rs3 = await db.update`update users set password = ${password} where username = ${username};`
console.log(rs3);
// 关闭数据库连接
db.end();